package database.app;

import database.common.ConnectionFactory;
import model.app.Person;

import java.sql.*;
/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   1/3/13
 * Time:   8:10 PM
 */
public class PersonDao {
    private static final String PERSON_INSERT =
            "INSERT INTO app.person (emri, mbiemri, funksioni, password, enabled ) VALUES (?,?,?,?,? )";
    private static final String PERSON_UPDATE =
            "UPDATE app.person SET emri = ?, mbiemi = ?, funksioni = ?, password = ?, enabled = ? WHERE id  = ?";
    private static final String SELECT_SINGLE_PERSON =
            " SELECT id, emri, mbiemri, funksioni, password, enabled FROM app.person WHERE id = ? ";
    private static final String SELECT_LIST_PERSONS =
            "SELECT query_to_xml('SELECT id, emri, mbiemri, funksioni, enabled FROM app.person WHERE lower(emri) LIKE lower'||(?||') AND lower(mbiemri) LIKE lower('||?||') ORDER BY emri, mbiemri LIMIT 50',true,false,'')";

    public static int insert(Person person){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(PERSON_INSERT);
            statement.setString(1, person.getEmri());
            statement.setString(2, person.getMbiemri());
            statement.setString(3, person.getFunksioni());
            statement.setString(4, person.getPassword());
            statement.setInt(5, person.getEnabled());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int update(Person person){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(PERSON_UPDATE);
            if (person.getId()<1){
                return -1;
            }
            statement.setString(1, person.getEmri());
            statement.setString(2, person.getMbiemri());
            statement.setString(3, person.getFunksioni());
            statement.setString(4, person.getPassword());
            statement.setInt(5, person.getEnabled());
            statement.setInt(6, person.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Person selectSinglePerson(String id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Person person = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_PERSON);
            statement.setString(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                person = new Person();
                person.setId(resultSet.getInt(1));
                person.setEmri(resultSet.getString(2));
                person.setMbiemri(resultSet.getString(3));
                person.setFunksioni(resultSet.getString(4));
                person.setPassword(resultSet.getString(5));
                person.setEnabled(resultSet.getInt(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return person;
    }

    public static String selecttListPersonLikeXML(String emri, String mbiemri){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String result ="";
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_LIST_PERSONS);
            statement.setString(1, emri.trim());
            statement.setString(2, mbiemri.trim());
            resultSet = statement.executeQuery();
            if (resultSet.next()){
               result = resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
